{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Load statistics to CSV\n",
    "school_raw = pd.read_csv('FY16-17 Admission Stats Raw.csv')\n",
    "\n",
    "# Update column label\n",
    "school_raw.rename(columns={'School Name: School Name' : 'School Name'}, inplace=True)\n",
    "\n",
    "# Filter only for Accept, Defer Accept, Waitlist Accept\n",
    "school_raw = school_raw.loc[((school_raw['Application Result'] == 'Accept') | (school_raw['Application Result'] == 'Defer Accept') | (school_raw['Application Result'] == 'Waitlist Accept'))]\n",
    "\n",
    "# Add SAT, TOEFL Total\n",
    "school_raw['SAT Total'] = school_raw['SAT New Superscore Reading'] + school_raw['SAT New Superscore Math']\n",
    "school_raw['TOEFL Total'] = school_raw['TOEFL Superscore Reading'] + school_raw['TOEFL Superscore Speaking'] + school_raw['TOEFL Superscore Listening'] + school_raw['TOEFL Superscore Writing']\n",
    "\n",
    "# Replace 0 with NaN for total value\n",
    "school_raw['SAT Total'].replace(0, np.nan, inplace=True)\n",
    "school_raw['TOEFL Total'].replace(0, np.nan, inplace=True)\n",
    "\n",
    "# Save to FY16-17 Admission Result Cleaned.csv\n",
    "school_raw.to_csv('FY16-17 Admission Stats Cleaned.csv', sep=',', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create a speical table for safety schools\n",
    "school_normal_raw = school_raw.loc[school_raw['Category'] != 'Safety School']\n",
    "school_reach_raw = school_raw.loc[school_raw['Category'] == 'Reach School']\n",
    "school_match_raw = school_raw.loc[school_raw['Category'] == 'Solid School']\n",
    "school_safety_raw = school_raw.loc[school_raw['Category'] == 'Safety School']\n",
    "\n",
    "# Group records by School Name\n",
    "school_grouped_raw = school_raw.groupby('School Name')\n",
    "school_grouped_normal = school_normal_raw.groupby('School Name')\n",
    "school_grouped_reach = school_reach_raw.groupby('School Name')\n",
    "school_grouped_match = school_match_raw.groupby('School Name')\n",
    "school_grouped_safety = school_safety_raw.groupby('School Name')\n",
    "\n",
    "# Create new dataframe and insert statistics\n",
    "school_stats = pd.DataFrame()\n",
    "#school_stats['Count'] = school_grouped_raw['School Name'].count()\n",
    "school_stats['Normal Count'] = school_grouped_normal['School Name'].count() # Reach + Match\n",
    "#school_stats['Reach Count'] = school_grouped_reach['School Name'].count()\n",
    "#school_stats['Match Count'] = school_grouped_match['School Name'].count()\n",
    "\n",
    "school_stats['SAT Mean'] = school_grouped_normal['SAT Total'].mean().round()\n",
    "school_stats['SAT Median'] = school_grouped_normal['SAT Total'].median()\n",
    "school_stats['SAT Std'] = school_grouped_normal['SAT Total'].std().round();\n",
    "school_stats['SAT Min'] = school_grouped_normal['SAT Total'].min()\n",
    "school_stats['SAT Max'] = school_grouped_normal['SAT Total'].max()\n",
    "school_stats['TOEFL Mean'] = school_grouped_normal['TOEFL Total'].mean().round()\n",
    "school_stats['TOEFL Median'] = school_grouped_normal['TOEFL Total'].median()\n",
    "school_stats['TOEFL Std'] = school_grouped_normal['TOEFL Total'].std().round();\n",
    "school_stats['TOEFL Min'] = school_grouped_normal['TOEFL Total'].min()\n",
    "school_stats['TOEFL Max'] = school_grouped_normal['TOEFL Total'].max()\n",
    "school_stats['Safety Count'] = school_grouped_safety['Category'].count()\n",
    "school_stats['Safety SAT Mean'] = school_grouped_safety['SAT Total'].mean().round()\n",
    "school_stats['Safety SAT Std'] = school_grouped_safety['SAT Total'].std().round();\n",
    "school_stats['Safety TOEFL Mean'] = school_grouped_safety['TOEFL Total'].mean().round()\n",
    "school_stats['Safety TOEFL Std'] = school_grouped_safety['TOEFL Total'].std().round();\n",
    "\n",
    "# Fill NaN with 0\n",
    "school_stats.fillna(0, inplace=True)\n",
    "\n",
    "# Filter out result that only has 1 record or less\n",
    "# school_stats = school_stats.loc[school_stats['Count'] >= 4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Save statistics to CSV\n",
    "school_stats.to_csv('FY16-17 Statistics.csv', sep=',', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
